USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetOptionsByQuestion]    Script Date: 10/31/2011 10:49:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[GetOptionsByQuestion]
@questionID int
as
begin
/**********************************************************
* @Name: GetOptionsByQuestion
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Retrieves options for a provided question.
* @Parameters: 
*	@questionID - filter questions by questionID.
* @Result: 
*    0 - if no questions exist
*    1 - if questions exist
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @questionID IS NULL
    begin
	  raiserror 20000 'Error [GetOptionsByQuestion] - Invalid question ID!'
    end   
    
    if @returnValue = 0
    begin
         SELECT *
           FROM dbo.QuestionOption
          WHERE [QuestionID] = @questionID
          ORDER BY OptionSortOrder      
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetOptionsByQuestion] - Unable to retrieve options!'
         end   
    end  

  return @returnValue
end





GO

/**************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetPassword]    Script Date: 10/31/2011 10:49:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[GetPassword]
@email nvarchar(50)
as
begin
/**********************************************************
* @Name: GetPassword
* @Database: Users
*
* @Description: 
*	Returns password for given user's email.
* @Parameters: 
*	@email - email address of user.
* @Result: 
*    0 - if no user exists
*    1 - if user exists
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @email IS NULL
    begin
	  raiserror 20000 'Error [GetPassword] - Invalid email address!'
    end   
    
    if @returnValue = 0
    begin
         SELECT Password
           FROM dbo.Users
          WHERE Email = @email                   
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetPassword] - Unable to retrieve user password!'
         end   
    end  

  return @returnValue
end



GO

/**********************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetQuestionsByCategory]    Script Date: 10/31/2011 10:49:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[GetQuestionsByCategory]
@categoryID int
as
begin
/**********************************************************
* @Name: GetQuestionsByCategory
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Retrieves questions for a provided category.
* @Parameters: 
*	@categoryID - filter questions by categoryID.
* @Result: 
*    0 - if no questions exist
*    1 - if questions exist
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @categoryID IS NULL
    begin
	  raiserror 20000 'Error [GetQuestionsByCategory] - Invalid category ID!'
    end   
    
    if @returnValue = 0
    begin
         SELECT *
           FROM dbo.Question a
          WHERE a.QuestionCategoryID = @categoryID
		  AND a.QuestionActive = 1
          ORDER BY a.QuestionSortOrder      
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetQuestionsByCategory] - Unable to retrieve questions!'
         end   
    end  

  return @returnValue
end




GO

/****************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetSpecialsByOption]    Script Date: 10/31/2011 10:49:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[GetSpecialsByOption]
@optionID int
as
begin
/**********************************************************
* @Name: GetSpecialsByOption
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Retrieves specials for a provided option.
* @Parameters: 
*	@questionID - filter specials by optionID.
* @Result: 
*    0 - if no questions exist
*    1 - if questions exist
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @optionID IS NULL
    begin
	  raiserror 20000 'Error [GetSpecialsByOption] - Invalid option ID!'
    end   
    
    if @returnValue = 0
    begin
         SELECT *
           FROM dbo.QuestionOptionSpecial
          WHERE [OptionID] = @optionID      
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetSpecialsByOption] - Unable to retrieve specials!'
         end   
    end  

  return @returnValue
end





GO

/*******************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetUserAnswers]    Script Date: 10/31/2011 10:50:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[GetUserAnswers]
@userID int
as
begin
/**********************************************************
* @Name: GetUserAnswers
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Retrieves answers to questions for a provided user.
* @Parameters: 
*	@categoryID - filter questions by userID.
* @Result: 
*    0 - if no questions exist
*    1 - if questions exist
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @userID IS NULL
    begin
	  raiserror 20000 'Error [GetUserAnswers] - Invalid user ID!'
    end   
    
    if @returnValue = 0
    begin
         SELECT *
           FROM dbo.QuestionAnswer a
          WHERE a.UserID = @userID        
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetUserAnswers] - Unable to retrieve answers!'
         end   
    end  

  return @returnValue
end





GO

/*******************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[GetUserInfo]    Script Date: 10/31/2011 10:50:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[GetUserInfo]
@email nvarchar(50),
@password nvarchar(50)
as
begin
/**********************************************************
* @Name: GetUserInfo
* @Database: Users
*
* @Description: 
*	Checks if the user exits in table or not.
* @Parameters: 
*	@username - username of the user.
* @Result: 
*    0 - if no user exists
*    1 - if user exists
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @email IS NULL
    begin
	  raiserror 20000 'Error [GetUserInfo] - Invalid email address!'
    end   
    
    if @password IS NULL
    begin
	  raiserror 20050 'Error [GetUserInfo] - Invalid [password!'
    end

    if @returnValue = 0
    begin
         SELECT *
           FROM dbo.Users
          WHERE Email = @email 
            AND Password = @password         
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [GetUserInfo] - Unable to retrieve valid user!'
         end   
    end  

  return @returnValue
end



GO

/***********************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[InsertOptionSpecial]    Script Date: 10/31/2011 10:50:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[InsertOptionSpecial]
@optionID				int,
@specialName			varchar(MAX),
@specialDisplayType		varchar(MAX)
as
begin
/**********************************************************
* @Name: InsertOptionSpecial
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert option option.
* @Parameters: 
*	@optionID				id for option
*	@specialName			name of the special field
*	@specialDisplayType		display type for the session
* @Result: 
*    0 - add failed
*    @@Identity - add succeeded
*********************************************************/
    declare @returnValue as int
    declare @returnError as int
    set @returnValue = 0
    
    if @optionID IS NULL
    begin
	  raiserror 20000 'Error [InsertOptionSpecial] - Invalid option ID!'
    end  
    
    if @specialName IS NULL
    begin
	  raiserror 20000 'Error [InsertOptionSpecial] - Invalid special name!'
    end  
    
    if @specialDisplayType IS NULL
    begin
	  raiserror 20000 'Error [InsertOptionSpecial] - Invalid special display type!'
    end 
    
    if @returnValue = 0
    begin
         INSERT INTO dbo.QuestionOptionSpecial ([OptionID], [SpecialName], [SpecialDisplayType]) 
	     VALUES (@optionID, @specialName, @specialDisplayType)                                           
                 
         set @returnValue = @@identity

		 set @returnError = @@ERROR

		 if @returnError <> 0
         begin
             raiserror 20100 'Error [InsertOptionSpecial] - Unable to insert special!'
         end
         
         if @returnValue = 0
         begin
             raiserror 20100 'Error [InsertOptionSpecial] - Unable to insert special!'
         end   
    end  

  return @returnValue
end








GO

/********************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[InsertQuestion]    Script Date: 10/31/2011 10:50:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[InsertQuestion]
@questionCategoryID		int,
@questionSortOrder		int,
@displayType			varchar(150),
@fieldName				varchar(MAX),
@fieldLabel				varchar(MAX),
@fieldCSS				varchar(MAX),
@fieldSuffix			varchar(MAX),
@fieldHelpText			varchar(150),
@fieldHelpUrl			varchar(150)
as
begin
/**********************************************************
* @Name: InsertQuestion
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@questionCategoryID		category id for question
*	@questionSortOrder		order for question listing
*	@displayType			type of display for question
*   @fieldName				name of field
*	@fieldLabel				text caption for field
*	@fieldCSS				CSS style for field
*	@fieldSuffix			suffix for field
*	@fieldHelpText			help text for field
*	@fieldHelpUrl			URL for help screen
* @Result: 
*    0 - add failed
*    @@Identity - add succeeded
*********************************************************/
    declare @returnValue as int
    declare @returnError as int
    set @returnValue = 0
    
    if @questionCategoryID IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestion] - Invalid question category ID!'
    end  
    
    if @questionSortOrder IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestion] - Invalid question sort order!'
    end  
    
    if @displayType IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestion] - Invalid disaply type!'
    end 
    
    if @fieldName IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestion] - Invalid field name!'
    end 
    
    if @fieldLabel IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestion] - Invalid field label!'
    end 
    
    if @returnValue = 0
    begin
         INSERT INTO dbo.Question ([QuestionCategoryID], [QuestionSortOrder], [DisplayType], [FieldName], [FieldLabel], [FieldCSS], [FieldSuffix], [FieldHelpText], [FieldHelpURL], [QuestionActive]) 
	     VALUES (@questionCategoryID, @questionSortOrder, @displayType, @fieldName, @fieldLabel, @fieldCSS, @fieldSuffix, @fieldHelpText, @fieldHelpUrl, 1)                                           
                 
         set @returnValue = @@identity

		 set @returnError = @@ERROR

		 if @returnError <> 0
         begin
             raiserror 20100 'Error [InsertQuestion] - Unable to insert question!'
         end
         
         if @returnValue = 0
         begin
             raiserror 20100 'Error [InsertQuestion] - Unable to insert question!'
         end   
    end  

  return @returnValue
end






GO

/*********************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[InsertQuestionOption]    Script Date: 10/31/2011 10:50:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[InsertQuestionOption]
@questionID				int,
@optionName				varchar(MAX),
@optionSortOrder		int,
@optionLabel			varchar(MAX)
as
begin
/**********************************************************
* @Name: InsertQuestionOption
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question option.
* @Parameters: 
*	@questionID				id for question
*	@optionName				name of the option field
*	@optionSortOrder		order for option listing
*	@optionLabel			text caption for field
* @Result: 
*    0 - add failed
*    @@Identity - add succeeded
*********************************************************/
    declare @returnValue as int
    declare @returnError as int
    set @returnValue = 0
    
    if @questionID IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestionOption] - Invalid question ID!'
    end  
    
    if @optionName IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestionOption] - Invalid option name!'
    end  
    
    if @optionSortOrder IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestionOption] - Invalid option sort order!'
    end 
    
    if @optionLabel IS NULL
    begin
	  raiserror 20000 'Error [InsertQuestionOption] - Invalid option label!'
    end 
    
    if @returnValue = 0
    begin
         INSERT INTO dbo.QuestionOption ([QuestionID], [OptionName], [OptionSortOrder], [OptionLabel], [OptionActive]) 
	     VALUES (@questionID, @optionName, @optionSortOrder, @optionLabel, 1)                                           
                 
         set @returnValue = @@identity

		 set @returnError = @@ERROR

		 if @returnError <> 0
         begin
             raiserror 20100 'Error [InsertQuestionOption] - Unable to insert option!'
         end
         
         if @returnValue = 0
         begin
             raiserror 20100 'Error [InsertQuestionOption] - Unable to insert option!'
         end   
    end  

  return @returnValue
end







GO

/*********************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[InsertUserAnswers]    Script Date: 10/31/2011 10:51:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[InsertUserAnswers]
@questionID int,
@userID		int,
@fieldName		varchar(MAX),
@fieldValue		varchar(MAX)
as
begin
/**********************************************************
* @Name: InsertUserAnswers
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert answer to question for user.
* @Parameters: 
*	@questionID - question identifier.
*   @userID - user identifier.
*   @fieldName - the name of the field on the form.
*   @fieldValue - the entry for the field from the user.
* @Result: 
*    0 - add failed
*    @@Identity - add succeeded
*********************************************************/
    declare @returnValue as int
    declare @returnError as int
    set @returnValue = 0
    
    if @questionID IS NULL
    begin
	  raiserror 20000 'Error [InsertUserAnswers] - Invalid question ID!'
    end  
    
    if @userID IS NULL
    begin
	  raiserror 20000 'Error [InsertUserAnswers] - Invalid user ID!'
    end  
    
    if @fieldName IS NULL
    begin
	  raiserror 20000 'Error [InsertUserAnswers] - Invalid field name!'
    end 
    
    if @fieldValue IS NULL
    begin
	  raiserror 20000 'Error [InsertUserAnswers] - Invalid field value!'
    end 
    
    
    
    if @returnValue = 0
    begin
		if EXISTS (SELECT * FROM dbo.QuestionAnswer WITH (READUNCOMMITTED) WHERE [QuestionID] = @questionID AND [UserID] = @userID AND [FieldName] = @fieldName)
		BEGIN
			declare @recAnswerID int
			
			SELECT @recAnswerID = [ANSWERID]
			FROM dbo.QuestionAnswer WITH (READUNCOMMITTED)
			WHERE [QuestionID] = @questionID AND [UserID] = @userID AND [FieldName] = @fieldName
		
			UPDATE dbo.QuestionAnswer SET [FieldValue] = @fieldValue
			WHERE [AnswerID] = @recAnswerID AND [FieldName] = @fieldName                                          
                 
			set @returnValue = @@ROWCOUNT

			 if @returnValue = 0
			 begin
				 raiserror 20100 'Error [UpdateUserAnswers] - Unable to update answer!'
			 end 
		END
		ELSE
		BEGIN
         INSERT INTO dbo.QuestionAnswer ([QuestionID], [UserID], [FieldName], [FieldValue]) 
	     VALUES (@questionID, @userID, @fieldName, @fieldValue)                                           
                 
         set @returnValue = @@identity

		 set @returnError = @@ERROR

		 if @returnError <> 0
         begin
             raiserror 20100 'Error [RegisterUser] - Unable to insert answer!'
         end
         
         if @returnValue = 0
         begin
             raiserror 20100 'Error [RegisterUser] - Unable to insert answer!'
         end
        END
    end  

  return @returnValue
end

GO

/*******************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[RegisterUser]    Script Date: 10/31/2011 10:51:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE procedure [dbo].[RegisterUser]
@email nvarchar(50),
@password		nvarchar(50),
@firstname		nvarchar(50),
@lastname		nvarchar(50),
@birthDate		nvarchar(50),
@gender			nvarchar(1),
@ethnicity		nvarchar(50),
@streetAddress	nvarchar(50),
@city			nvarchar(50),
@state			nvarchar(50),
@zipCode		nvarchar(50),
@mobilePhone	nvarchar(50),
@country		nvarchar(50),
@textFlag		nvarchar(1)
as
begin
/**********************************************************
* @Name: RegisterUser
* @Database: Users
*
* @Description: 
*	Returns password for given user's email.
* @Parameters: 
*	@email - email address of user.
* @Result: 
*    0 - if no user exists
*    1 - if user exists
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @email IS NULL
    begin
	  raiserror 20000 'Error [RegisterUser] - Invalid email address!'
    end   
    
    if @returnValue = 0
    begin
         INSERT INTO Users ([Email], [Password], [Firstname], [Lastname], [BirthDate], [Gender], [Ethnicity], [StreetAddress], [City], [State], [ZipCode], [MobilePhone], [Country], [TextFlag]) 
	     VALUES (@email, @password, @firstname, @lastname, @birthDate, @gender, @ethnicity, NULL,@city, NULL, @zipCode,@mobilePhone,NULL,@textFlag)                                           
                 
         set @returnValue = @@error

         if @returnValue <> 0
         begin
             raiserror 20100 'Error [RegisterUser] - Unable to retrieve user password!'
         end   
    end  

  return @returnValue
end




GO

/***************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateQuestion]    Script Date: 10/31/2011 10:51:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[UpdateQuestion]
@questionID				int,
@questionCategoryID		int,
@questionSortOrder		int,
@displayType			varchar(150),
@fieldLabel				varchar(MAX),
@fieldCSS				varchar(MAX),
@fieldSuffix			varchar(MAX),
@fieldHelpText			varchar(150),
@fieldHelpUrl			varchar(150)
as
begin
/**********************************************************
* @Name: UpdateQuestion
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@questionID				question identifier
*	@questionCategoryID		category id for question
*	@questionSortOrder		order for question listing
*	@displayType			type of display for question
*	@fieldLabel				text caption for field
*	@fieldCSS				CSS style for field
*	@fieldSuffix			suffix for field
*	@fieldHelpText			help text for field
*	@fieldHelpUrl			URL for help screen
* @Result: 
*    0 - update failed
*    1 - update succeeded
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @questionID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestion] - Invalid question ID!'
    end 
    
    if @questionCategoryID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestion] - Invalid question category ID!'
    end  
    
    if @questionSortOrder IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestion] - Invalid question sort order!'
    end  
    
    if @displayType IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestion] - Invalid display type!'
    end 
    
    if @fieldLabel IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestion] - Invalid field label!'
    end 
    
    if @returnValue = 0
    begin
         UPDATE dbo.Question SET [QuestionCategoryID] = @questionCategoryID, [QuestionSortOrder] = @questionSortOrder, 
			[DisplayType] = @displayType, [FieldLabel] = @fieldLabel, [FieldCSS] = @fieldCSS, [FieldSuffix] = @fieldSuffix, 
			[FieldHelpText] = @fieldHelpText, [FieldHelpURL] = @fieldHelpUrl
		 WHERE [QuestionID] = @questionID  
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateQuestion] - Unable to update question!'
         end   
    end  

  return @returnValue
end


GO

/**************************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateQuestionActiveStatus]    Script Date: 10/31/2011 10:51:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE procedure [dbo].[UpdateQuestionActiveStatus]
@questionID				int
as
begin
/**********************************************************
* @Name: UpdateQuestionActiveStatus
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@questionID				question identifier
* @Result: 
*    0 - update failed
*    1 - update succeeded
*********************************************************/
    declare @returnValue as int
    declare @status as bit
    set @returnValue = 0
    
    if @questionID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionActiveStatus] - Invalid question ID!'
    end 
    
    if @returnValue = 0
    begin
		SET NOCOUNT ON
		SELECT @status = [QuestionActive]  
		FROM dbo.Question
		WHERE [QuestionID] = @questionID
		
		IF @status = 1
			set @status = 0
		ELSE
			set @status = 1
    
		SET NOCOUNT OFF
		
         UPDATE dbo.Question SET [QuestionActive] = @status
		 WHERE [QuestionID] = @questionID  
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateQuestionActiveStatus] - Unable to update question!'
         end   
    end  

  return @returnValue
end


GO

/******************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateQuestionOption]    Script Date: 10/31/2011 10:51:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE procedure [dbo].[UpdateQuestionOption]
@optionID				int,
@optionSortOrder		int,
@optionLabel			varchar(MAX)
as
begin
/**********************************************************
* @Name: UpdateQuestionOption
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@optionID				option identifier
*	@optionSortOrder		order for option listing
*	@optionLabel			text caption for option
* @Result: 
*    0 - update failed
*    1 - update succeeded
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @optionID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOption] - Invalid option ID!'
    end 
    
    if @optionSortOrder IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOption] - Invalid option sort order!'
    end  
    
    if @optionLabel IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOption] - Invalid option label!'
    end 
    
    if @returnValue = 0
    begin
         UPDATE dbo.QuestionOption SET [OptionSortOrder] = @optionSortOrder, [OptionLabel] = @optionLabel
		 WHERE [OptionID] = @optionID  
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateQuestionOption] - Unable to update option!'
         end   
    end  

  return @returnValue
end



GO

/**************************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateQuestionOptionActiveStatus]    Script Date: 10/31/2011 10:52:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[UpdateQuestionOptionActiveStatus]
@optionID				int
as
begin
/**********************************************************
* @Name: UpdateQuestionOptionActiveStatus
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@optionID				option identifier
* @Result: 
*    0 - update failed
*    1 - update succeeded
*********************************************************/
    declare @returnValue as int
    declare @status as bit
    set @returnValue = 0
    
    if @optionID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOptionActiveStatus] - Invalid option ID!'
    end 
    
    
    if @returnValue = 0
    begin
         SET NOCOUNT ON
		SELECT @status = [OptionActive]  
		FROM dbo.QuestionOption
		WHERE [OptionID] = @optionID
		
		IF @status = 1
			set @status = 0
		ELSE
			set @status = 1
    
		SET NOCOUNT OFF
         
         UPDATE dbo.QuestionOption SET [OptionActive] = @status
		 WHERE [OptionID] = @optionID  
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateQuestionOptionActiveStatus] - Unable to update option status!'
         end   
    end  

  return @returnValue
end



GO

/***********************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateQuestionOptionSpecial]    Script Date: 10/31/2011 10:52:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[UpdateQuestionOptionSpecial]
@specialID				int,
@specialDisplayType		varchar(MAX)
as
begin
/**********************************************************
* @Name: UpdateQuestionOptionSpecial
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Insert question.
* @Parameters: 
*	@specialID				special identifier
*	@specialDisplayType		special type for display
* @Result: 
*    0 - update failed
*    1 - update succeeded
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @specialID IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOptionSpecial] - Invalid special ID!'
    end 
    
    if @specialDisplayType IS NULL
    begin
	  raiserror 20000 'Error [UpdateQuestionOptionSpecial] - Invalid special display type!'
    end  
    
    if @returnValue = 0
    begin
         UPDATE dbo.QuestionOptionSpecial SET [SpecialDisplayType] = @specialDisplayType
         WHERE [SpecialID] = @specialID  
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateQuestionOptionSpecial] - Unable to update special!'
         end   
    end  

  return @returnValue
end




GO

/*************************************************/
USE [csc640_fall2011_team3]
GO

/****** Object:  StoredProcedure [dbo].[UpdateUserAnswers]    Script Date: 10/31/2011 10:52:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[UpdateUserAnswers]
@answerID int,
@fieldName		varchar(MAX),
@fieldValue		varchar(MAX)
as
begin
/**********************************************************
* @Name: UpdateUserAnswers
* @Database: csc640_fall2011_team3
*
* @Description: 
*	Update answer to question by answer ID.
* @Parameters: 
*	@answerID - question identifier.
*   @fieldName - the name of the field on the form.
*   @fieldValue - the entry for the field from the user.
* @Result: 
*    0 - add failed
*    1 - add succeeded
*********************************************************/
    declare @returnValue as int
    set @returnValue = 0
    
    if @answerID IS NULL
    begin
	  raiserror 20000 'Error [UpdateUserAnswers] - Invalid answer ID!'
    end  
    
    if @fieldName IS NULL
    begin
	  raiserror 20000 'Error [UpdateUserAnswers] - Invalid field name!'
    end 
    
    if @fieldValue IS NULL
    begin
	  raiserror 20000 'Error [UpdateUserAnswers] - Invalid field value!'
    end 
    
    if @returnValue = 0
    begin
         UPDATE dbo.QuestionAnswer SET [FieldValue] = @fieldValue
	     WHERE [AnswerID] = @answerID AND [FieldName] = @fieldName                                          
                 
         set @returnValue = @@ROWCOUNT

		 if @returnValue = 0
         begin
             raiserror 20100 'Error [UpdateUserAnswers] - Unable to update answer!'
         end   
    end  

  return @returnValue
end





GO



